import json
import sqlite3
from datetime import datetime

import pymysql
from record_log import record_log


def get_daily_report():
    print('获取时间')
    # 获取当前系统时间
    current_time = datetime.now()

    # 格式化时间
    formatted_time = current_time.strftime('%Y-%m-%d %H:%M:%S')

    # 输出格式化后的时间
    print(formatted_time)
    # 配置数据库
    config = {
        "host": "172.16.9.34",
        "port": 9030,
        "user": "baoer.liang",
        "password": "wi2024#^_",
        "database": "ADS"
    }
    try:
        # 连接数据库
        connection = pymysql.connect(
            host=config["host"],
            port=config["port"],
            user=config["user"],
            password=config["password"],
            database=config["database"]
        )
        # 创建游标对象
        cursor = connection.cursor()

        # 执行SQL查询
        sql_query = """
                SELECT DEPARTNAME, CREATEDBY, DBCTEXTAREA1, SUBMITAT
                FROM  ADS_DAILY_REPORT_DA
                WHERE SUBMITAT >= NOW() - INTERVAL 1 DAY;
            """
        # sql_query = """
        #         SELECT DEPARTNAME, CREATEDBY, DBCTEXTAREA1, SUBMITAT
        #         FROM  ADS_DAILY_REPORT_DA
        #         WHERE SUBMITAT >= '2024-09-14' - INTERVAL 1 DAY;
        #     """
        cursor.execute(sql_query)

        # 获取查询结果
        result = cursor.fetchall()

        # 将查询结果插入到本地数据库中
        if result:
            # 将 SUBMITAT 字段转换为字符串格式
            formatted_result = [(row[0], row[1], row[2], row[3].strftime('%Y-%m-%d %H:%M:%S')) for row in result]
            insert_into_local_db(formatted_result)
            return True
        else:
            print("没有查询到近一天内更新的数据")
            record_log("没有查询到近一天内更新的数据", "info")
            return False

    except pymysql.MySQLError as e:
        print(f"数据库查询失败: {e}")
        record_log(f"数据库查询失败: {e}", "error")
        return None


def insert_into_local_db(data):
    # 连接到本地SQLite数据库（如果数据库不存在，则会自动创建）
    conn = sqlite3.connect('/home/xc/zy/sales_top3_project/top3_sales_processing/local_daily_report.db')
    cursor = conn.cursor()

    # 删除旧表（如果存在），重新创建表
    cursor.execute("DROP TABLE IF EXISTS daily_report")

    # 创建表（重新创建以包含 SUBMITAT 列）
    create_table_query = """
       CREATE TABLE IF NOT EXISTS daily_report (
           id INTEGER PRIMARY KEY AUTOINCREMENT,
           DEPARTNAME TEXT,
           CREATEDBY TEXT,
           DBCTEXTAREA1 TEXT,
           SUBMITAT TEXT  -- 使用 TEXT 类型以存储日期时间格式
       );
       """
    cursor.execute(create_table_query)

    # 插入数据
    insert_query = """
       INSERT INTO daily_report (DEPARTNAME, CREATEDBY, DBCTEXTAREA1, SUBMITAT)
       VALUES (?, ?, ?, ?);
       """
    cursor.executemany(insert_query, data)
    conn.commit()

    print(f"{cursor.rowcount} 条记录插入到 ../local_daily_report.db 数据库中的 daily_report 表中")
    record_log(f"{cursor.rowcount} 条记录插入到 ../local_daily_report.db 数据库中的 daily_report 表中", "info")

    # 关闭本地数据库连接
    conn.close()


# 查询实际更新TOP3问题
def query_tops_questions():
    # 连接到 SQLite 数据库
    conn = sqlite3.connect('/home/xc/zy/sales_top3_project/top3_sales_processing/local_daily_report.db')
    cursor = conn.cursor()

    try:
        # 如果表存在，则删除表
        drop_table_query = "DROP TABLE IF EXISTS tops_question;"
        cursor.execute(drop_table_query)

        # 创建 tops_question 表
        create_table_query = """
                CREATE TABLE tops_question (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    DEPARTNAME TEXT,
                    CREATEDBY TEXT,
                    DBCTEXTAREA1 TEXT,
                    SUBMITAT TEXT
                );
                """
        cursor.execute(create_table_query)

        # 查询 DBCTEXTAREA1 列中字符数大于7的数据行
        query = """
                SELECT DEPARTNAME, CREATEDBY, DBCTEXTAREA1, SUBMITAT FROM daily_report
                WHERE LENGTH(DBCTEXTAREA1) > 7;
                """
        cursor.execute(query)

        # 获取查询结果
        results = cursor.fetchall()

        # 将结果插入到 tops_question 表中
        insert_query = """
                INSERT INTO tops_question (DEPARTNAME, CREATEDBY, DBCTEXTAREA1, SUBMITAT)
                VALUES (?, ?, ?, ?);
                """

        for row in results:
            cursor.execute(insert_query, row)

        # 提交事务
        conn.commit()

    except sqlite3.Error as e:
        print(f"An error occurred: {e}")
        record_log(f"查询实际更新的top3问题失败: {e}", "error")
        conn.rollback()

    finally:
        # 关闭数据库连接
        cursor.close()
        conn.close()


# 检查数据库中是否存在列
def column_exists(cursor, table_name, column_name):
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = [col[1] for col in cursor.fetchall()]
    return column_name in columns


if __name__ == '__main__':
    get_daily_report()
    query_tops_questions()
